---
title: "cp_policy_in_group stored procedure documentation"
summary: "Retrieved SQL Server procedure that counts whether a policy belongs to a group or chapter."
status: draft
owner: "SAIF Claims BOF team"
last_reviewed: "2026-08-25"
source_schema: "dbo"
source_name: "cp_policy_in_group"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_policy_in_group"
retrieval_date: "2026-08-25"
---
# cp_policy_in_group

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `dbo` |
| **Type** | Standalone procedure |
| **Database** | SQL Server (`eisD0P11`) |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicySecurity` |

## Purpose

`cp_policy_in_group` answers the group-user access question by counting employer-group memberships for a policy. The retrieved body first decides whether the request should use parent-group, group-only, or group-plus-chapter logic, then returns a `howMany` count for the selected branch.

## Procedures / Methods

### cp_policy_in_group

| Field | Value |
| --- | --- |
| **Returns** | Result set containing `howMany` membership count |
| **Called by** | `clsPolicySecurity.IsInGroup` |
| **Description** | Returns a membership count for the supplied policy, group key, and optional chapter filter. |

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@groupID` | IN | `varchar(10)` | — | Parent or group key to evaluate. |
| `@chapters` | IN | `varchar(10)` | — | Optional chapter filter. |
| `@policyNum` | IN | `int` | — | Policy number to test. |

## Business Rules

The retrieved database source embeds the following server-side rules.

| Rule ID | Summary |
| --- | --- |
| [BR-VP-046](#br-vp-046-policy-in-group-membership-chooses-a-parent%2C-group%2C-or-group-and-chapter-query-path) | `@UseSql` is set to 1, 2, or 3 based on whether the input key matches a parent group, a group, or a group plus chapter filter. |
| [BR-VP-047](#br-vp-047-ordinary-group-key-detection-excludes-parent-group-rows) | Group-key detection ignores rows whose `GRP_TYP_CD = 'PARNT'`. |
| [BR-VP-048](#br-vp-048-policy-in-group-returns-membership-as-a-count-and-applies-chapter-filtering-only-in-branch-three) | Each branch returns `count(*)`; the chapter filter is added only when `@UseSql = 3`. |

### BR-VP-046 Policy-in-group membership chooses a parent, group, or group-and-chapter query path

| Field | Value |
| --- | --- |
| **Statement** | The membership-check procedure selects a parent-group, group-only, or group-plus-chapter query path by inspecting the supplied group key and whether a chapter string was supplied. |
| **Trigger** | `cp_policy_in_group` executes. |
| **Enforcement** | The procedure probes `WCS_EMPLOYER_GROUP` to set `@UseSql`, then runs the matching `if @UseSql = ...` `SELECT count(*)` block. |
| **Outcome** | Only one membership-count query runs for the supplied key and chapter combination. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` |

---

### BR-VP-047 Ordinary group-key detection excludes parent-group rows

| Field | Value |
| --- | --- |
| **Statement** | When deciding whether the supplied key should be treated as a regular group key, the procedure excludes employer-group rows whose `GRP_TYP_CD = 'PARNT'`. |
| **Trigger** | `cp_policy_in_group` evaluates the group-key branch. |
| **Enforcement** | The `grp_assn_key` probe applies `AND grp_typ_cd <> 'PARNT'` before setting `@UseSql = 2`. |
| **Outcome** | Parent-group-only keys are not misclassified as ordinary group keys. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` |

---

### BR-VP-048 Policy-in-group returns membership as a count and applies chapter filtering only in branch three

| Field | Value |
| --- | --- |
| **Statement** | The membership-check procedure returns a numeric `howMany` count for every branch, and the chapter filter applies only in the group-plus-chapter branch. |
| **Trigger** | `cp_policy_in_group` executes. |
| **Enforcement** | Each branch selects `count(*) howMany`, while only the `@UseSql = 3` branch adds `GroupCheck.chapCd like rtrim(@chapters)`. |
| **Outcome** | Callers receive a count-based membership answer, with chapter filtering limited to the explicit chapter branch. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` |

## Operations

The retrieved database source implements the following internal operations.

| Operation | Description | Trigger |
| --- | --- | --- |
| [Resolve the policy-in-group query branch](#resolve-the-policy-in-group-query-branch) | Determine whether the supplied key should use the parent, group-only, or group-plus-chapter membership query. | Caller invokes `cp_policy_in_group`. |
| [Count matching group memberships](#count-matching-group-memberships) | Return a `howMany` count for the selected branch based on the policies most recent covered period and employer-group associations. | A branch selected by `@UseSql` executes. |

### Resolve the policy-in-group query branch

| Field | Value |
| --- | --- |
| **Description** | Determine whether the supplied key should use the parent, group-only, or group-plus-chapter membership query. |
| **Trigger** | Caller invokes `cp_policy_in_group`. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` |

### Count matching group memberships

| Field | Value |
| --- | --- |
| **Description** | Return a `howMany` count for the selected branch based on the policies most recent covered period and employer-group associations. |
| **Trigger** | A branch selected by `@UseSql` executes. |
| **Source** | SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json` |

## Calling code references

| Class | Method | Source File |
| --- | --- | --- |
| `clsPolicySecurity` | `IsInGroup` | `SAIF.Business.View.Policy\clsPolicySecurity.vb` |

## Source Code

<details>
<summary>SQL Server procedure source</summary>

```sql

Create Procedure dbo.cp_policy_in_group 
--	LIST PARAMETERS

	@groupID varchar(10),
	@chapters varchar(10), 
	@policyNum int
AS

/*--------------------------------------------------------------------------------
Procedure Name:	  cp_policy_in_group
Date Written:	  8/21/2001
Author:		  Melinda Hillier (SAIF)
Installation:	  SAIF Corporation
Source File Name: cp_policy_in_group
Description:	  If the policy is a member of the group return
                  a count
Modifications:	05/09/02 - Removed the where condition for plcy_eff_dt
                	to be within 4 years of today's date.
		08/13/2003 - JERTOD:
			replaced EIS_SAIF_EMPR with VW_EIS_SAIF_EMPR
		09/17/2003 - JERTOD:
			replaced VW_EIS_SAIF_EMPR with WCS_Policy; changed ALTER to CREATE proc
			ALTER  Procedure dbo.cp_policy_in_group @groupNum int,
		        @chapters varchar(10), @policyNum int
		12/01/2004 - JERTOD:
			changed the proc to eliminate the need to use
			a group pms number as a paramter
			changed @groupNum parameter to a string for @groupID
			left the old select in. DELETE IT AT NEXT MODIFICATION
		02/27/2006 - JERTOD: updated for BDS Release 10
		08/21/2013 - JERTOD: changed @UseSql triage to correctly evaluate group parents. they now have an extra row in the 
								group table 
		8/21/2013: JERTOD changed the @GroupLevel evaluation to exclude group parents from being evaluated to a group
--------------------------------------------------------------------------------*/
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


DECLARE @UseSql int

set @UseSql=2

/* END THE PROCEDURE */



----------------------------------------------
--RELEASE 10

/************************/
/* INSPECT THE GROUPKEYCODE PASSED INTO PROC */
/************************/

--if we find the GroupKeyCode in the parent column we'll do the parent select
if (select count(distinct parent_grp) from eis_prod.dbo.wcs_employer_group 
	where ((parent_grp=rtrim(@groupID)) and (rtrim(@groupID)!='')))>0 
	begin
		set @UseSql=1
	end

--if we find the GroupKeyCode in the group column then we'll do the group select
if (select count(distinct grp_assn_key) from eis_prod.dbo.wcs_employer_group 
	where grp_assn_key=rtrim(@groupID)  AND grp_typ_cd <> 'PARNT' )>0 
	begin
		set @UseSql=2
	end

--unless the group key has only one entry, then we'll use its chapter code
--to discriminate it from the other keys 
--if it has more than one distinct chapter then we'll check all chapters
--unless the chapter code is specified
/*
if(select COUNT(DISTINCT WEG.pms_grp_sub_cd) 
		from eis_prod.dbo.WCS_EMPLOYER_GROUP WEG 
		where (WEG.grp_assn_key=@groupID) and (not weg.pms_grp_num is null))=1
begin
		set @chapters=(select DISTINCT WEG.pms_grp_sub_cd
		from eis_prod.dbo.WCS_EMPLOYER_GROUP WEG 
		where (WEG.grp_assn_key=@groupID) and (not weg.pms_grp_num is null))
		set @UseSql=3
end
*/

--if its a group and it has a chapter code we'll do the group chapter thingy
if (@UseSql=2) and (rtrim(@chapters)>'')
	begin
		set @UseSql=3
	end

/************************/
/* GROUP PARENT CODE PASSED IN */
/************************/
if @UseSql=1
SELECT 
--@UseSql	testnum,
count(*) howMany--,@UseSql whichOne
FROM
(select wGrp.parent_grp gKey from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
	on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
	on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
	on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

where GroupCheck.gKey=@GroupId

/************************/
/* GROUP WITHOUT A SPECIFIC CHAPTER CODE */
/************************/
if @UseSql=2
select 
--@UseSql	testnum,
count (*) howMany

FROM
(select wGrp.grp_assn_key gKey from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
	on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
	on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
	on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

WHERE 
GroupCheck.gKey=@GroupId


/************************/
/* GROUP WITH A SPECIFIC CHAPTER */
/************************/
if @UseSql=3
select 
--@UseSql	testnum,
count (*) howMany

FROM
(select wGrp.grp_assn_key gKey,wGrp.chap_cd chapCd,wGrp.emp_grp_id from 
EIS_PROD.dbo.VW_WCS_POLICY_NO_SUB  wPol
join EIS_PROD.dbo.VW_WCS_POLICY_PD_NO_SUB  polPer
	on wPol.plcy_num=polPer.plcy_num and polPer.MOST_RECENT_PP_COV_FL='Y'
join EIS_PROD.dbo.WCS_POLICY_PERIOD_SUM  pPdSum
	on pPdSum.plcy_pd_id=polPer.plcy_pd_id
join EIS_PROD.dbo.WCS_EMPLOYER_GROUP wGrp
	on wGrp.EMP_GRP_ID=pPdSum.EMP_GRP_ID
where wPol.plcy_num=@policyNum) as GroupCheck

WHERE 
GroupCheck.gKey=@GroupId
and GroupCheck.chapCd like rtrim(@chapters)

/* END THE PROCEDURE */



Return
```

</details>

## Related documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server retrieval JSON: `C:\Users\chrfre\AppData\Local\Temp\oracle-sources\cp_policy_in_group.json`
- Calling code: `SAIF.Business.View.Policy\clsPolicySecurity.vb`